/**
 * @Author: SevDaisy十七散人 
 * @Date: 2020-07-04 13:03:43
 */
package zyy.control;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

import zyy.model.BeanRider;
import zyy.model.BeanRider_Work;
import zyy.ui.FrmRiderInfo;
import zyy.util.BaseException;
import zyy.util.BusinessException;
import zyy.util.DBUtil;
import zyy.util.DbException;
import zyy.util.Bus;

public class RiderManager {
  public BeanRider login(String loginName) throws BaseException {
    if (loginName == null || "".equals(loginName) || loginName.length() > 50) {
      throw new BusinessException("登陆账号 必须是 1~255 个字符");
    }
    BeanRider result = null;
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT RiderID,rLevel from Rider where loginName=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setString(1, loginName);
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        result = new BeanRider();
        result.setRiderID(rs.getInt(1));
        result.setrLevel(rs.getString(2));
        result.setrName(loginName);
      } else {
        throw new BusinessException(String.format("查无此账号 rName:%s", loginName));
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    return result;
  }

  public void reg(String lName, String rName) throws BusinessException {
    // 先查询有没有重复lName，若无，则插入lName rName rTime rLevel
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT r.RiderID FROM Rider r WHERE r.loginName=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setString(1, lName);
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        throw new BusinessException("该账户名已被占用");
      }
      sql = "INSERT INTO Rider(loginName, rName, rLevel, rTime) values(?, ?, ?, ?)";
      pst = conn.prepareStatement(sql);
      pst.setString(1, lName);
      pst.setString(2, rName);
      pst.setString(3, "新人");
      pst.setTimestamp(4, new java.sql.Timestamp(System.currentTimeMillis()));
      pst.executeLargeUpdate();
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void del(int RiderID) throws BusinessException {
    // 先查，若有未完成的订单，则不能删除
    // 先删除已完成的工作条目
    // 再删除相应的骑手
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      conn.setAutoCommit(false);
      String sql = "SELECT count(o.OrderID) FROM Rider r LEFT JOIN Orders o ON o.oRiderID = r.RiderID WHERE o.oStatus NOT IN ('已确认收货','等待送单') AND r.RiderID=? GROUP BY r.RiderID";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, RiderID);
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next() && rs.getInt(1) > 0) {
        throw new BusinessException("该骑手尚有未完成的订单,不可删除");
      }
      sql = "DELETE FROM Rider_Work WHERE RiderID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, RiderID);
      pst.executeUpdate();
      sql = "DELETE FROM Rider WHERE RiderID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, RiderID);
      pst.executeUpdate();
      rs.close();
      pst.close();
      conn.commit();
    } catch (SQLException e) {
      System.out.println("出现SQL异常，开始回滚");
      try {
        conn.rollback();
        System.out.println("回滚成功");
      } catch (SQLException ex) {
        System.out.println("回滚失败！！！");
        throw new BusinessException(ex.getMessage());
      }
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public List<BeanRider> loadAll() throws BaseException {
    List<BeanRider> result = new ArrayList<BeanRider>();
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT RiderID, rName, rLevel, workCnt, loginName,rTime FROM riderinfo";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      java.sql.ResultSet rs = pst.executeQuery();
      while (rs.next()) {
        BeanRider p = new BeanRider();
        p.setRiderID(rs.getInt(1));
        p.setrName(rs.getString(2));
        p.setrLevel(rs.getString(3));
        p.setWorkCnt(rs.getInt(4));
        p.setlName(rs.getString(5));
        if (rs.getString(6) == null) {
          p.setrTime(null);
        } else {
          p.setrTime(Bus.sdf.parse(rs.getString(6)));
        }
        result.add(p);
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DbException(e);
    } catch (ParseException e) {
      e.printStackTrace();
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    return result;
  }

  public void loadRiderInfo(FrmRiderInfo f) throws BusinessException {
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String rLevel;
      String sql = "SELECT r.rName, r.loginName, r.rTime, r.rLevel FROM Rider r WHERE RiderID = ?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, Bus.currentRider.getRiderID());
      java.sql.ResultSet rs = pst.executeQuery();
      rs.next();
      f.rNameCon.setText(Bus.infoVal(rs.getString(1)));
      f.lNameCon.setText(Bus.infoVal(rs.getString(2)));
      f.regTimeCon.setText(Bus.infoVal(rs.getString(3)));
      rLevel = rs.getString(4);
      f.rLevelCon.setText(Bus.infoVal(rLevel));
      // sql = "SELECT COUNT(w.RiderWorkID), SUM(w.rwPayValue)
      // FROM Rider_Work w WHERE RiderID = ?";
      sql = "SELECT COUNT(w.OrderID), SUM(w.rwPayValue) FROM Rider_Work w WHERE RiderID = ?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, Bus.currentRider.getRiderID());
      rs = pst.executeQuery();
      rs.next();
      f.wCntCon.setText(Bus.infoVal(rs.getString(1)));
      f.wSumCon.setText(Bus.infoVal(rs.getString(2)));
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void add200() throws BusinessException {
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT COUNT(w.OrderID) FROM Rider_Work w WHERE RiderID = ?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, Bus.currentRider.getRiderID());
      java.sql.ResultSet rs = pst.executeQuery();
      rs.next();
      int wCnt = rs.getInt(1);
      sql = "SELECT rLevel FROM Rider WHERE RiderID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, Bus.currentRider.getRiderID());
      rs = pst.executeQuery();
      rs.next();
      String rLevel = rs.getString(1);
      Bus.currentRider.setrLevel(rLevel);
      sql = "INSERT INTO Orders( OrderID, UserID, oRiderID, oValue, oRealValue, creatTime, oStatus ) values( ?, 1, ?, 100, 10, '2020-5-20 11:11:11', '测试数据' )";
      pst = conn.prepareStatement(sql);
      pst.setInt(2, Bus.currentRider.getRiderID());
      for (int i = 0; i < 200; i++) {
        pst.setInt(1, wCnt + 1000 + i);
        pst.executeUpdate();
      }
      sql = "INSERT INTO Rider_Work(OrderID, RiderID, rwPayTime, rwPayValue) values(?, ?, '2020-5-20 11:11:11', ?)";
      pst = conn.prepareStatement(sql);
      pst.setInt(2, Bus.currentRider.getRiderID());
      double pay = 0;
      switch (rLevel) {
        case "新人":
          pay = 2;
          break;
        case "正式骑手":
          pay = 5;
          break;
        case "精英骑手":
          pay = 7;
          break;
        case "王牌骑手":
          pay = 8;
          break;
        default:
          break;
      }
      for (int i = 0; i < 200; i++) {
        pst.setInt(1, wCnt + 1000 + i);
        pst.setDouble(3, pay);
        pst.executeUpdate();
      }
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }

  }

  public void clearAdd() throws BusinessException {
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "DELETE FROM Orders WHERE oStatus='测试数据'";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.executeUpdate();
      sql = "DELETE FROM Rider_Work WHERE OrderID>=1000";
      pst = conn.prepareStatement(sql);
      pst.executeUpdate();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }

  }

  public void loadRiderLevel(FrmRiderInfo f) throws BusinessException {
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT COUNT(w.OrderID) FROM Rider_Work w WHERE RiderID = ?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, Bus.currentRider.getRiderID());
      java.sql.ResultSet rs = pst.executeQuery();
      rs.next();
      String rLevel;
      int wCnt = rs.getInt(1);
      if (0 < wCnt && wCnt <= 100) {
        rLevel = "新人";
      } else if (100 < wCnt && wCnt <= 450) {
        rLevel = "正式骑手";
      } else if (450 < wCnt && wCnt <= 650) {
        rLevel = "精英骑手";
      } else {
        rLevel = "王牌骑手";
      }
      sql = "UPDATE Rider SET rLevel=? WHERE RiderID=?";
      pst = conn.prepareStatement(sql);
      pst.setString(1, rLevel);
      pst.setInt(2, Bus.currentRider.getRiderID());
      pst.executeUpdate();
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }

  }

  public List<BeanRider_Work> loadWorks() throws BusinessException {
    List<BeanRider_Work> result = new ArrayList<BeanRider_Work>();
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT w.rwPayTime, w.rwPayValue,w.OrderID FROM Rider_Work w WHERE RiderID = ? ORDER BY w.OrderID ASC";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, Bus.currentRider.getRiderID());
      java.sql.ResultSet rs = pst.executeQuery();
      while (rs.next()) {
        BeanRider_Work p = new BeanRider_Work();
        p.setOrderID(rs.getInt(3));
        p.setRwPayTime(Bus.sdf.parse(rs.getString(1)));
        p.setRwPayValue(rs.getDouble(2));
        result.add(p);
      }
      sql = "SELECT v.rName, v.mName, v.mAddr, v.uAddr, v.uTelphone, v.oStatus FROM viewuserorder v WHERE RiderID=? AND v.OrderID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, Bus.currentRider.getRiderID());
      for (int i = 0; i < result.size(); i++) {
        pst.setInt(2, result.get(i).getOrderID());
        rs = pst.executeQuery();
        if (rs.next()) {
          result.get(i).setrName(rs.getString(1));
          result.get(i).setmName(rs.getString(2));
          result.get(i).setmAddr(rs.getString(3));
          result.get(i).setuAddr(rs.getString(4));
          result.get(i).setuTel(rs.getString(5));
          result.get(i).setoStatus(rs.getString(6));
        }
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } catch (ParseException e) {
      e.printStackTrace();
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    return result;
  }
}